package com.iris.live.services.report.extracter;

import com.iris.live.services.common.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * @Description:零售相关分析数据的抽取
 * @author: Tony.Zhang
 * @Date: 2017-07-19 8:59
 **/
@Service("RetailDataExtract")
public class RetailDataExtracterCommand extends DataExtractCommand {
    private static Logger logger = LoggerFactory.getLogger(RetailDataExtracterCommand.class);

    public RetailDataExtracterCommand() {

    }

    @Override
    public void execute(Date startDate, Date endDate) throws Exception {
        String statement = this.getExtractStatement(startDate, endDate);
        List<Map> listValue = this.getList(statement);
        if(listValue == null){
            logger.warn("No data found for sql = {}", statement);
            return;
        }

        for(Map value : listValue){

        }
    }

        /**
         *
          * @param permit
         * @param date
         * @return
         * @throws Exception
         */
    private Set<String> getExistedData(String permit, Date date) throws Exception{
        Set<String> values = new HashSet<>();
        String statement = "SELECT PERMIT, STAT_DATE, VEHICLE_MODEL FROM T_BI_RETAIL_INFO WHERE STAT_DATE = :statDate";

        Map<String, Object> params = new HashMap<>();
        params.put("statDate", DateFormatUtils.getFormatDateToString(date, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD));
        List<Map> resultSet = this.getList(statement, params);
        if(resultSet != null){
             for(Map result : resultSet){
                 values.add(result.get("PERMIT") + "_" + result.get("STAT_DATE") + "_" + result.get("VEHICLE_MODEL"));
             }
        }
        return values;
    }

    /**
     * 获取抽取零售指标原始值的脚本
     * @return
     */
    protected String getExtractStatement(Date startDate, Date endDate){
        String statement = "SELECT 'LSH' 集团, a.大区, a.大区_EN,a.小区, a.小区_EN, if(a.CTA is null or a.CTA ='','其他cta',a.CTA ) as CTA, if(a.CTA_EN is null or a.CTA_EN ='','Other CTA',a.CTA_EN ) as CTA_EN, a.许可_BMBS, a.经销商名,a.经销商名_EN, " +
                "   a.salesCode, a.来源,a.品牌, a.级别,   " +
                "   SUM(VEHICLE_GP_TGP.TYPE * (车辆销售价未税 - 消费税未税 - 采购价格未税 - (经销商赠送成本未税 + 延保赠送成本未税 + 精品赠送成本未税) +  厂家随车购置税 + 零售SI + 批发SI + 精品毛利未税 + 延保毛利未税 + 其他服务费未税 + 上牌服务费未税 + 金融服务费未税 + 金融手续费未税 + 保险手续费未税))  " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  " +
                "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_MODEL_SI_BMBS_调整.PT_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17   AS tgp, " +
                "   SUM(VEHICLE_GP_TGP.TYPE * (车辆销售价未税 - 消费税未税  + 精品销售价格未税 + 延保销售价格未税 + 其他服务费未税 + 上牌服务费未税  + 金融服务费未税 + 金融手续费未税 + 保险手续费未税)) " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_MODEL_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0) AS 销售毛收入,   " +
                "   0 - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17  " +
                "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust,0) /1.17 " +
                "   - IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust,0)/1.17  " +
                "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.R_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.W_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_BRAND_SI_BMBS_调整.PT_SI_AMOUNT, 0) " +
                "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17  AS 品牌调整值, " +
                "   IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 " +
                "   + IFNULL(VEHICLE_BRAND_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0) /1.17 AS 销售毛收入调整值 " +
                "   FROM kpi_allset a " +
                "   LEFT JOIN (   " +
                "       SELECT a.许可,  来源, g.品牌, g.级别,   " +
                "           CASE   " +
                "               WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d')  AND    " +
                "                    DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 0   " +
                "               WHEN DATE_FORMAT(a.开票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.开票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN 1   " +
                "               WHEN DATE_FORMAT(a.退票时间,'%Y-%m-%d') >= DATE_FORMAT('2000-01-01','%Y-%m-%d') AND DATE_FORMAT(a.退票时间,'%Y-%m-%d') <= DATE_FORMAT('2020-01-01','%Y-%m-%d') THEN -1   " +
                "           END TYPE,   " +
                "           IFNULL(a.折后价,0)/1.17 AS 车辆销售价未税,        " +
                "           IFNULL(a.消费税,0) / 1.17 AS 消费税未税,        " +
                "           case when a.所属经销商 = a.调入经销商 then ifnull(a.协商调车款项金额, 0)  else IFNULL(a.采购价格含税, 0) end /1.17 AS 采购价格未税,   " +
                "           IFNULL(d.经销商赠送成本,0) AS 经销商赠送成本未税,        " +
                "           IFNULL(g.延保赠送成本,0)/1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_adjust, 0)/1.17 AS 延保赠送成本未税,        " +
                "           IFNULL(f.精品赠送成本,0)/1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_adjust, 0)/1.17 AS 精品赠送成本未税,      " +
                "		    IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.PT_SI_AMOUNT,IFNULL(BMBS_随车.厂家随车购置税, 0)), 0) AS 厂家随车购置税,      " +
                "           IFNULL(h.其他服务费,0) / 1.06 AS 其他服务费未税,        " +
                "           IFNULL(i.金融服务费,0) / 1.06 AS 金融服务费未税,        " +
                "           IFNULL(i.金融手续费,0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.finance_poundage_adjust, 0)/1.17 AS 金融手续费未税,        " +
                "           IFNULL(j.交强险手续费,0) / 1.17 + IFNULL(j.商业险手续费,0) / 1.17 + IFNULL(VEHICLE_INCOME_ADJUSTMENT.insurance_poundage_adjust, 0)/1.17AS 保险手续费未税,        " +
                "           IFNULL(k.置换评估费,0) / 1.17 AS 置换评估费未税,        " +
                "           IFNULL(n.延保毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.five_stars_extend_cost_buy_adjust, 0)/1.17AS 延保毛利未税,        " +
                "           IFNULL(n.延保销售价格, 0) / 1.17 AS 延保销售价格未税,        " +
                "           IFNULL(ll.精品毛利, 0) / 1.17 - IFNULL(VEHICLE_INCOME_ADJUSTMENT.after_market_product_cost_buy_adjust,0)/1.17 AS 精品毛利未税,      " +
                "           IFNULL(ll.精品销售价格, 0) / 1.17 AS 精品销售价格未税,   " +
                "           IFNULL(IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.R_SI_AMOUNT,z.零售SI), snp_setting.预估零售SI),0) AS 零售SI,  " +
                "           IFNULL(IF(a.所属经销商 = a.调入经销商, 0, IFNULL(IFNULL(VEHICLE_SI_BMBS_调整.W_SI_AMOUNT, Z.`批发SI`), snp_setting.预估批售SI)),0) AS `批发SI`,      " +
                "           IFNULL(hh.上牌服务费, 0) / 1.06 AS 上牌服务费未税      " +
                "       FROM vw_retailed_quotation a    " +
                "       LEFT JOIN 库存利润表 z on a.库存编号 = z.库存编号 " +
                "       LEFT JOIN (            " +
                "         SELECT SUM(随车成本) AS 经销商赠送成本, 报价编号 FROM 报价随车表            " +
                "         WHERE 随车类型 = '经销商' AND 随车部门 = '销售部' GROUP BY 报价编号            " +
                "       ) d ON d.报价编号 = a.报价编号            " +
                "       LEFT JOIN (            " +
                "         SELECT 报价编号, SUM(IF(随车名称 <> '购置税',随车成本,0)) AS 厂家随车SI成本," +
                "                         SUM(IF(随车名称 = '购置税',随车成本,0)) AS 厂家随车购置税 " +
                "         FROM 报价随车表  WHERE 随车类型 = '厂家' " +
                "         GROUP BY 报价编号            " +
                "       ) BMBS_随车 ON BMBS_随车.报价编号 = a.报价编号          " +
                "       LEFT JOIN (        " +
                "         SELECT  编号, sum(成本 * 数量) 精品赠送成本   " +
                "           FROM 报价精品详细表 WHERE   折后价 = 0 GROUP BY  编号  " +
                "       ) f ON f.编号 = a.精品        " +
                "       LEFT JOIN (        " +
                "         SELECT SUM(IFNULL(a.`折后价` * 数量, 0)) 精品销售价格, SUM(IFNULL(a.`折后价` * 数量, 0)) - SUM(IFNULL(a.成本 * 数量, 0)) AS 精品毛利 ,a.编号       " +
                "         FROM 报价精品详细表 a WHERE 折后价 > 0 GROUP BY a.`编号`       " +
                "       ) ll ON ll.编号 = a.精品        " +
                "       LEFT JOIN (          " +
                "         SELECT 延保编号, IFNULL(延保原价,0) AS 延保赠送成本      " +
                "         FROM 报价五星延保 WHERE 延保折后价 = 0      " +
                "       ) g ON g.延保编号 = a.延保编号          " +
                "       LEFT JOIN (          " +
                "          SELECT 延保编号, IFNULL(延保折后价,0) 延保销售价格, (IFNULL(延保折后价,0) - IFNULL(延保原价,0)) AS 延保毛利          " +
                "          FROM 报价五星延保  WHERE 延保折后价 > 0    " +
                "       ) n ON n.延保编号 = a.延保编号           " +
                "       LEFT JOIN (        " +
                "         SELECT a.费用编号, SUM(b.费用折后价) AS 其他服务费        " +
                "         FROM 报价费用表 a JOIN 报价费用详细表 b ON b.费用编号 = a.费用编号        " +
                "         WHERE b.费用名称 = '其他服务费'        " +
                "         GROUP BY a.费用编号        " +
                "       ) h ON h.费用编号 = a.费用编号      " +
                "       LEFT JOIN (        " +
                "         SELECT a.费用编号, SUM(b.费用折后价) AS 上牌服务费        " +
                "         FROM 报价费用表 a  JOIN 报价费用详细表 b ON b.费用编号 = a.费用编号        " +
                "         WHERE b.费用名称 = '上牌服务费'        " +
                "         GROUP BY a.费用编号        " +
                "       ) hh ON hh.费用编号 = a.费用编号      " +
                "       LEFT JOIN 报价金融表 i ON i.金融编号 = a.金融        " +
                "       LEFT JOIN 报价保险表 j ON j.保险编号 = a.保险编号      " +
                "       LEFT JOIN 报价置换表 k ON k.置换编号 = a.置换编号  " +
                "       LEFT JOIN (  " +
                "           SELECT PERMIT, STOCK_CODE,   " +
                "                  SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "                  SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
                "                  SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
                "                  SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
                "                  SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust, 0)) five_stars_extend_cost_adjust,   " +
                "                  SUM(IF(IS_GIVE=0, five_stars_extend_cost_adjust, 0)) five_stars_extend_cost_buy_adjust   " +
                "           FROM INCOME_ADJUSTMENT   " +
                "           WHERE TYPE = 1  " +
                "           GROUP BY PERMIT, STOCK_CODE " +
                "        ) VEHICLE_INCOME_ADJUSTMENT ON VEHICLE_INCOME_ADJUSTMENT.PERMIT =a.许可 AND VEHICLE_INCOME_ADJUSTMENT.STOCK_CODE = a.库存编号  " +
                "        LEFT JOIN (  " +
                "           SELECT PERMIT, STOCK_ID, " +
                "                  SUM(IF(SI_NAME_CN = '零售SI', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "                  SUM(IF(SI_NAME_CN = '批售SI', SI_AMOUNT, 0)) W_SI_AMOUNT, " +
                "                  SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
                "           FROM SI_BMBS_SETTING A , SI_SETTING B    " +
                "           WHERE A.SET_TYPE = 1 AND A.SI_SETTING_ID = B.ID  " +
                "           GROUP BY PERMIT, STOCK_ID   " +
                "        ) VEHICLE_SI_BMBS_调整 ON VEHICLE_SI_BMBS_调整.PERMIT =a.许可 AND VEHICLE_SI_BMBS_调整.STOCK_ID = a.库存编号 " +
                "       LEFT JOIN (   " +
                "           SELECT permit, carCode, BMBS_RETAIL_SI AS 预估零售SI, BMBS_WHOLESALE_SI AS 预估批售SI  " +
                "           FROM snp_setting  WHERE y_m = '190001'   " +
                "       ) snp_setting ON snp_setting.carCode = a.车型编号 and snp_setting.permit=a.许可   " +
                "       ,LSH车型 g      " +
                "       WHERE  ((date_format(a.开票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.开票时间,'%Y-%m-%d') >= '2000-01-01')      " +
                "         OR ((date_format(a.退票时间,'%Y-%m-%d') <= '2020-01-01'  AND date_format(a.退票时间,'%Y-%m-%d') >= '2000-01-01')))   " +
                "          AND (CASE WHEN 'MB' = '' THEN TRUE ELSE g.品牌 = 'MB' END)    " +
                "          AND a.车型编号 = g.车型编号   " +
                "   ) VEHICLE_GP_TGP ON a.许可_BMBS = VEHICLE_GP_TGP.许可 AND a.来源 = VEHICLE_GP_TGP.来源 AND a.品牌 = VEHICLE_GP_TGP.品牌 AND  a.级别 = VEHICLE_GP_TGP.级别 " +
                "   LEFT JOIN (" +
                "        SELECT PERMIT, 来源, 品牌, 级别,   " +
                "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "               SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
                "               SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
                "               SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, 0, five_stars_extend_cost_adjust)) five_stars_extend_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust,0)) five_stars_extend_cost_adjust   " +
                "        FROM INCOME_ADJUSTMENT, LSH车型   " +
                "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号 AND (车型 IS NULL OR 车型 ='') " +
                "          AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "          AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "        GROUP BY PERMIT, 来源, 品牌, 级别  " +
                "   ) VEHICLE_MODEL_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_MODEL_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_MODEL_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_MODEL_INCOME_ADJUSTMENT.品牌 AND  a.级别 = VEHICLE_MODEL_INCOME_ADJUSTMENT.级别 " +
                "   LEFT JOIN (" +
                "        SELECT PERMIT, 来源, 品牌,   " +
                "               SUM(finance_poundage_adjust) finance_poundage_adjust, " +
                "               SUM(insurance_poundage_adjust) insurance_poundage_adjust,   " +
                "               SUM(after_market_product_cost_adjust) after_market_product_cost_adjust, " +
                "               SUM(after_market_product_cost_buy_adjust) after_market_product_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, 0, five_stars_extend_cost_adjust)) five_stars_extend_cost_buy_adjust, " +
                "               SUM(IF(IS_GIVE=1, five_stars_extend_cost_adjust,0)) five_stars_extend_cost_adjust   " +
                "        FROM INCOME_ADJUSTMENT, LSH车型   " +
                "        WHERE INCOME_ADJUSTMENT.TYPE = 2 AND STOCK_CODE IS NULL AND CAR_CODE = 车型编号  AND (级别 IS NULL OR 级别 ='') " +
                "          AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m')  " +
                "          AND DATE_FORMAT(ADJUST_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "        GROUP BY PERMIT, 来源, 品牌  " +
                "   ) VEHICLE_BRAND_INCOME_ADJUSTMENT ON a.许可_BMBS = VEHICLE_BRAND_INCOME_ADJUSTMENT.PERMIT AND a.来源 = VEHICLE_BRAND_INCOME_ADJUSTMENT.来源 AND a.品牌 = VEHICLE_BRAND_INCOME_ADJUSTMENT.品牌 " +
                "   LEFT JOIN (  " +
                "        SELECT PERMIT, 来源, 品牌, 级别, SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "               SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
                "        FROM SI_BMBS_SETTING, LSH车型    " +
                "        WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (车型 IS NULL OR 车型 ='') " +
                "            AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "            AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "        GROUP BY PERMIT, 来源, 品牌, 级别   " +
                "   ) VEHICLE_MODEL_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_MODEL_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_MODEL_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_MODEL_SI_BMBS_调整.品牌  AND a.级别 = VEHICLE_MODEL_SI_BMBS_调整.级别" +
                "   LEFT JOIN (  " +
                "        SELECT PERMIT, 来源, 品牌, SUM(IF(SI_BMBS_CODE='R', SI_AMOUNT, 0)) R_SI_AMOUNT, " +
                "               SUM(IF(SI_BMBS_CODE='W', SI_AMOUNT, 0)) W_SI_AMOUNT, SUM(IF(SI_BMBS_CODE='PT', SI_AMOUNT, 0)) PT_SI_AMOUNT   " +
                "        FROM SI_BMBS_SETTING, LSH车型    " +
                "        WHERE (SI_BMBS_CODE='R' OR SI_BMBS_CODE='W' OR SI_BMBS_CODE='PT') AND SET_TYPE = 2 AND (级别 IS NULL OR 级别 ='') " +
                "           AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') >= DATE_FORMAT('2000-01-01','%Y-%m') " +
                "           AND DATE_FORMAT(VALID_START_DATE,'%Y-%m') <= DATE_FORMAT('2020-01-01','%Y-%m') " +
                "        GROUP BY PERMIT, 来源, 品牌   " +
                "   ) VEHICLE_BRAND_SI_BMBS_调整 ON a.许可_BMBS = VEHICLE_BRAND_SI_BMBS_调整.PERMIT AND a.来源 = VEHICLE_BRAND_SI_BMBS_调整.来源 AND a.品牌 = VEHICLE_BRAND_SI_BMBS_调整.品牌  " +
                "  WHERE  (CASE WHEN 'MB' = '' THEN TRUE ELSE a.品牌 = 'MB' END)  " +
                "  GROUP BY a.许可_BMBS,a.来源,a.品牌,a.级别 ";

        return statement;
    }
}
